/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     2018/9/14 14:31:17                           */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BookInformatica') and o.name = 'FK_BOOKINFO_REFERENCE_BOOKTYPE')
alter table BookInformatica
   drop constraint FK_BOOKINFO_REFERENCE_BOOKTYPE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BookNumber') and o.name = 'FK_BOOKNUMB_REFERENCE_BOOKINFO')
alter table BookNumber
   drop constraint FK_BOOKNUMB_REFERENCE_BOOKINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Collections') and o.name = 'FK_COLLECTI_REFERENCE_USERINFO')
alter table Collections
   drop constraint FK_COLLECTI_REFERENCE_USERINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Collections') and o.name = 'FK_COLLECTI_REFERENCE_BOOKINFO')
alter table Collections
   drop constraint FK_COLLECTI_REFERENCE_BOOKINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Comment') and o.name = 'FK_COMMENT_REFERENCE_USERINFO')
alter table Comment
   drop constraint FK_COMMENT_REFERENCE_USERINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Comment') and o.name = 'FK_COMMENT_REFERENCE_BOOKINFO')
alter table Comment
   drop constraint FK_COMMENT_REFERENCE_BOOKINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('FeedBack') and o.name = 'FK_FEEDBACK_REFERENCE_BOOKINFO')
alter table FeedBack
   drop constraint FK_FEEDBACK_REFERENCE_BOOKINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('FeedBack') and o.name = 'FK_FEEDBACK_REFERENCE_USERINFO')
alter table FeedBack
   drop constraint FK_FEEDBACK_REFERENCE_USERINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('OrderDetails') and o.name = 'FK_ORDERDET_REFERENCE_ORDERINF')
alter table OrderDetails
   drop constraint FK_ORDERDET_REFERENCE_ORDERINF
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('OrderDetails') and o.name = 'FK_ORDERDET_REFERENCE_BOOKINFO')
alter table OrderDetails
   drop constraint FK_ORDERDET_REFERENCE_BOOKINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('OrderInformatica') and o.name = 'FK_ORDERINF_REFERENCE_USERINFO')
alter table OrderInformatica
   drop constraint FK_ORDERINF_REFERENCE_USERINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ShoppingAddress') and o.name = 'FK_SHOPPING_REFERENCE_USERINFO')
alter table ShoppingAddress
   drop constraint FK_SHOPPING_REFERENCE_USERINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ShoppingCart') and o.name = 'FK_SHOPPING_REFERENCE_USERINFO2')
alter table ShoppingCart
   drop constraint FK_SHOPPING_REFERENCE_USERINFO2
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ShoppingCart') and o.name = 'FK_SHOPPING_REFERENCE_BOOKINFO')
alter table ShoppingCart
   drop constraint FK_SHOPPING_REFERENCE_BOOKINFO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BookInformatica')
            and   type = 'U')
   drop table BookInformatica
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BookNumber')
            and   type = 'U')
   drop table BookNumber
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BookType')
            and   type = 'U')
   drop table BookType
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Collections')
            and   type = 'U')
   drop table Collections
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Comment')
            and   type = 'U')
   drop table Comment
go

if exists (select 1
            from  sysobjects
           where  id = object_id('FeedBack')
            and   type = 'U')
   drop table FeedBack
go

if exists (select 1
            from  sysobjects
           where  id = object_id('OrderDetails')
            and   type = 'U')
   drop table OrderDetails
go

if exists (select 1
            from  sysobjects
           where  id = object_id('OrderInformatica')
            and   type = 'U')
   drop table OrderInformatica
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ShoppingAddress')
            and   type = 'U')
   drop table ShoppingAddress
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ShoppingCart')
            and   type = 'U')
   drop table ShoppingCart
go

if exists (select 1
            from  sysobjects
           where  id = object_id('UserInfo')
            and   type = 'U')
   drop table UserInfo
go

/*==============================================================*/
/* Table: BookInformatica                                       */
/*==============================================================*/
create table BookInformatica (
   BookId               int                  identity,
   BookName             nvarchar(50)         null,
   BookPress            nvarchar(50)         null,
   BookAuthor           nvarchar(50)         null,
   BookDescribe         nvarchar(50)         null,
   BookPicture          nvarchar(50)         null,
   BookPoster           nvarchar(50)         null,
   BookDetails          nvarchar(50)         null,
   BookPrice            money                null,
   TypeID               int                  null,
   BookNumber           nvarchar(50)         null,
   BookState            int                  null,
   BookTime             datetime             null,
   constraint PK_BOOKINFORMATICA primary key (BookId)
)
go

/*==============================================================*/
/* Table: BookNumber                                            */
/*==============================================================*/
create table BookNumber (
   NumberId             int                  identity,
   BookId               int                  null,
   BookCount            int                  null,
   SelledCount          int                  null,
   constraint PK_BOOKNUMBER primary key (NumberId)
)
go

/*==============================================================*/
/* Table: BookType                                              */
/*==============================================================*/
create table BookType (
   TypeID               int                  identity,
   TypeName             nvarchar(20)         null,
   FatherId             int                  not null,
   constraint PK_BOOKTYPE primary key (TypeID)
)
go

/*==============================================================*/
/* Table: Collections                                           */
/*==============================================================*/
create table Collections (
   CollectionId         int                  identity,
   UserId               int                  null,
   BookId               int                  null,
   constraint PK_COLLECTIONS primary key (CollectionId)
)
go

/*==============================================================*/
/* Table: Comment                                               */
/*==============================================================*/
create table Comment (
   CommentId            int                  identity,
   BookId               int                  null,
   UserId               int                  null,
   CommentValue         nvarchar(Max)        null,
   CommentState         int                  null,
   CommentDate          time                 null,
   constraint PK_COMMENT primary key (CommentId)
)
go

/*==============================================================*/
/* Table: FeedBack                                              */
/*==============================================================*/
create table FeedBack (
   FeedBackId           int                  identity,
   UserId               int                  null,
   BookId               int                  null,
   FeedBackValue        nvarchar(Max)        null,
   FeedBackDate         datetime             null,
   FeedBackState        int                  null,
   constraint PK_FEEDBACK primary key (FeedBackId)
)
go

/*==============================================================*/
/* Table: OrderDetails                                          */
/*==============================================================*/
create table OrderDetails (
   OrderId              nvarchar(50)         null,
   BookId               int                  null,
   Count                int                  null,
   BookPrice            money                null
)
go

/*==============================================================*/
/* Table: OrderInformatica                                      */
/*==============================================================*/
create table OrderInformatica (
   OrderId              nvarchar(50)         not null,
   OrderDate            datetime             null,
   OrderState           int                  null,
   UserId               int                  null,
   OrderAmount          money                null,
   OrderAddress         nvarchar(50)         null,
   constraint PK_ORDERINFORMATICA primary key (OrderId)
)
go

/*==============================================================*/
/* Table: ShoppingAddress                                       */
/*==============================================================*/
create table ShoppingAddress (
   AddressId            int                  identity,
   UserID               int                  null,
   AddressValue         nvarchar(50)         null,
   AddressState         int                  null,
   constraint PK_SHOPPINGADDRESS primary key (AddressId)
)
go

/*==============================================================*/
/* Table: ShoppingCart                                          */
/*==============================================================*/
create table ShoppingCart (
   CartId               int                  identity,
   UserID               int                  null,
   BookId               int                  null,
   CartCount            int                  null,
   constraint PK_SHOPPINGCART primary key (CartId)
)
go

/*==============================================================*/
/* Table: UserInfo                                              */
/*==============================================================*/
create table UserInfo (
   UserId               int                  identity,
   UserName             nvarchar(50)         null,
   UserPwd              nvarchar(50)         null,
   UserPower            int                  null,
   UserState            int                  null,
   InfoName             nvarchar(50)         null,
   InfoSex              int                  null,
   InfoDate             datetime             null,
   InfoMail             nvarchar(50)         null,
   InfoPhone            nvarchar(50)         null,
   InfoAddress          nvarchar(50)         null,
   constraint PK_USERINFO primary key (UserId)
)
go
/*==============================================================*/
/* Table: [Area]                                              */
/*==============================================================*/
CREATE TABLE [dbo].[Area](
[codeID] [char](6) NOT NULL,
 [name] [nvarchar](50) NOT NULL,
 [parentID] [char](6) NOT NULL
) ON [PRIMARY]
GO

alter table BookInformatica
   add constraint FK_BOOKINFO_REFERENCE_BOOKTYPE foreign key (TypeID)
      references BookType (TypeID)
go

alter table BookNumber
   add constraint FK_BOOKNUMB_REFERENCE_BOOKINFO foreign key (BookId)
      references BookInformatica (BookId)
go

alter table Collections
   add constraint FK_COLLECTI_REFERENCE_USERINFO foreign key (UserId)
      references UserInfo (UserId)
go

alter table Collections
   add constraint FK_COLLECTI_REFERENCE_BOOKINFO foreign key (BookId)
      references BookInformatica (BookId)
go

alter table Comment
   add constraint FK_COMMENT_REFERENCE_USERINFO foreign key (UserId)
      references UserInfo (UserId)
go

alter table Comment
   add constraint FK_COMMENT_REFERENCE_BOOKINFO foreign key (BookId)
      references BookInformatica (BookId)
go

alter table FeedBack
   add constraint FK_FEEDBACK_REFERENCE_BOOKINFO foreign key (BookId)
      references BookInformatica (BookId)
go

alter table FeedBack
   add constraint FK_FEEDBACK_REFERENCE_USERINFO foreign key (UserId)
      references UserInfo (UserId)
go

alter table OrderDetails
   add constraint FK_ORDERDET_REFERENCE_ORDERINF foreign key (OrderId)
      references OrderInformatica (OrderId)
go

alter table OrderDetails
   add constraint FK_ORDERDET_REFERENCE_BOOKINFO foreign key (BookId)
      references BookInformatica (BookId)
go

alter table OrderInformatica
   add constraint FK_ORDERINF_REFERENCE_USERINFO foreign key (UserId)
      references UserInfo (UserId)
go

alter table ShoppingAddress
   add constraint FK_SHOPPING_REFERENCE_USERINFO foreign key (UserID)
      references UserInfo (UserId)
go

alter table ShoppingCart
   add constraint FK_SHOPPING_REFERENCE_USERINFO2 foreign key (UserID)
      references UserInfo (UserId)
go

alter table ShoppingCart
   add constraint FK_SHOPPING_REFERENCE_BOOKINFO foreign key (BookId)
      references BookInformatica (BookId)
go

